from django.test import TestCase

# Create your tests here.


import os

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "djangoProject.settings")
    import django

    django.setup()

    from app01 import models

    # QuerySet对象.query 可以查看查询出的sql语句

    # # 一对多外键增删改查
    # # 增
    # # 1 直接写实际字段值
    # models.Book.objects.create(title='三国演义',price=32,publish_id=2)
    # # 2 虚拟字段 对象
    # publish_obj=models.Publish.objects.filter(pk=2).first()
    # models.Book.objects.create(title='三国演义', price=32, publish=publish_obj)

    # # 删
    # models.Publish.objects.filter(pk=2).delete() # 级联删除

    # # 改
    # # 1 直接写实际字段值
    # models.Book.objects.filter(pk=5).update(publish_id=3)
    # # # 2 虚拟字段 对象
    # publish_obj = models.Publish.objects.filter(pk=3).first()
    # models.Book.objects.filter(pk=5).update(publish=publish_obj)

    # # 多对多外键增删改查
    # # 增 同样支持对象
    # # 给书籍添加作者
    # book_obj = models.Book.objects.filter(pk=5).first()
    # book_obj.authors.add(2)  # 绑定作者为2
    # book_obj.authors.add(2, 3)  # 多对多绑定多个作者

    # # 删 同样支持对象
    # # 给书籍添加作者
    # book_obj = models.Book.objects.filter(pk=5).first()
    # book_obj.authors.remove(2)  # 绑定作者为2

    # # 改 支持对象
    # book_obj = models.Book.objects.filter(pk=5).first()
    # book_obj.authors.set([1,3])  # 传入可迭代对象
    # # set 没有会添加，有相同不修改，不同删除再新增

    # # 清空
    # # 清空某个书和作者的关系全部清除
    # book_obj = models.Book.objects.filter(pk=5).first()
    # book_obj.authors.clear()

    # 多表查询
    # 正向查询 外键在自己  正向查询按字段
    # 反向查询 外键在另一表  反向查询按表名小写

    # # 正向查询
    # # 1.查询书籍主键为1的出版社
    # book_obj = models.Book.objects.filter(pk=1).first()
    # # 通过书点可以直接拿到出版社对象
    # res = book_obj.publish
    # print(res)  # publish object
    # print(res.name)
    # print(res.addr)

    # # 2.查询书籍主键为2的作者
    # book_obj = models.Book.objects.filter(pk=2)
    # # 一个书有多个作者，需要.all()
    # res = book_obj.authors.all()
    # print(res)

    # # 3.查询作者tom的电话号码
    # author_obj = models.Author.objects.filter(name='tom').first()
    # res = author_obj.author_detail
    # print(res.phone)

    # # 反向查询 当查询结果多个时候需要_set
    # # 4.查询出版社北京出版社的书
    # publish_obj=models.Publish.objects.filter(name='北京出版社').first()
    # # 反向查询 表名小写_set
    # res = publish_obj.book_set.all()
    # print(res)

    # # 5.查询作者是jack写过的书
    # author_obj = models.Author.objects.filter(name='jack').first()
    # res = author_obj.book_set.all()
    # print(res)

    # # 6.查询手机号是1111的作者
    # author_detail_obj = models.AuthorDetail.objects.filter(phone=1111).first()
    # res = author_detail_obj.author
    # print(res)

    # # 基于双下划线的跨表查询
    # # 正向 外键字段__属性
    # # 1.查询kk的手机号和姓名
    # res = models.Author.objects.filter(name='kk').values('name','author_detail__phone')
    # print(res)
    # # 反向  表名小写__属性
    # res = models.AuthorDetail.objects.filter(author__name='kk').values('phone','author__name')
    # print(res)

    # # 2.查询书籍主键为1的出版社名称和书
    # # 正向
    # res = models.Book.objects.filter(pk=1).values('title','publish__name')
    # print(res)
    # # 反向
    # res = models.Publish.objects.filter(book__pk=1).values('name','book__title')
    # print(res)

    # # 3.查询书籍主键为1的作者姓名
    # # 正向
    # res = models.Book.objects.filter(pk=1).values('authors__name')
    # print(res)
    # # 反向
    # res = models.Author.objects.filter(book__pk=1).values('name')
    # print(res)

    # # 4.查询书籍主键为1的作者的手机号
    # res = models.Book.objects.filter(pk=1).values('authors__author_detail__phone')
    # print(res)

    # # 聚合查询
    from django.db.models import Max, Min, Sum, Count, Avg

    #
    # res = models.Book.objects.aggregate(Avg('price'))
    # print(res)
    #
    # res = models.Book.objects.aggregate(Max('price'), Min('price'), Sum('price'), Count('price'))
    # print(res)

    # # 分组查询
    # # 1.统计每一本书的作者个数
    # # res = models.Book.objects.annotate()  # models点什么，就按什么分组
    # res = models.Book.objects.annotate(author_num=Count('authors')).values('title', 'author_num')  # models点什么，就按什么分组
    # print(res)

    # # 2.统计每个出版社卖得最便宜的书
    # res = models.Publish.objects.annotate(min_price=Min('book__price')).values('name','min_price')
    # print(res)

    # # 3.统计不只一个作者的图书
    # res = models.Book.objects.annotate(author_num=Count('authors')).filter(author_num__gt=1).values('title')
    # print(res)

    # # 4.查询每个作者出的书的总价格
    # res = models.Author.objects.annotate(sum_price=Sum('book__price')).values('name','sum_price')
    # print(res)

    # 指定字段分组 如果报错关闭数据库严格模式
    # res = models.Author.objects.values('book__price').annotate()

    # # choice获取
    # user_obj = models.User.objects.filter(pk=3).first()
    # print(user_obj)
    # print(user_obj.get_gender_display())

    models.User.objects.filter(pk__gt=1000).delete()
